package com.alibaba.ai.dao;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import com.alibaba.ai.entity.YForum;

import java.util.List;

/**
 *
 * 论坛
 */
@Repository
public interface YForumMapper {

    /*@Select("select f.yForName,f.yForCount from Y_Forum f  where f.yPost.yPostPubtime = to_days(now()) Order by f.yForCount DESC Limit 10")
    List<YForum> getForumList();*/

     //论坛排行-——按日
    @Select("select f.Y_FOR_NAME,f.Y_FOR_COUNT from Y_FORUM f INNER JOIN y_POST p on f.Y_FOR_ID = p.Y_POST_ID where p.Y_POST_PUBTIME=(select to_char(sysdate,'yyyy-MM-dd') from dual) order by f.y_for_count DESC")
    List<YForum> getForumListByDay();

    //论坛排行-——按周
    @Select("select f.Y_FOR_NAME,f.Y_FOR_COUNT from Y_FORUM f INNER JOIN y_POST p on f.Y_FOR_ID = p.Y_POST_ID where p.Y_POST_PUBTIME=(select to_char(sysdate,'yyyy-MM') from dual) order by f.y_for_count DESC")
    List<YForum> getForumListByWeek();


    //论坛排行-——按年
    @Select("select f.Y_FOR_NAME,f.Y_FOR_COUNT from Y_FORUM f INNER JOIN y_POST p on f.Y_FOR_ID = p.Y_POST_ID where p.Y_POST_PUBTIME=(select to_char(sysdate,'yyyy-MM') from dual) order by f.y_for_count DESC")
    List<YForum> getForumListByYear();



    //搜索论坛-——当搜索框是空值得时候
    @Select("select f.Y_FOR_NAME,f.Y_FOR_COUNT,g.Y_GRA_NAME,g.y_gra_image from Y_FORUM f INNER JOIN Y_GRADES g on f.Y_GRA_ID = g.Y_GRA_ID  order by f.Y_FOR_COUNT DESC")
    List<YForum> getForumList();

    //搜索论坛————生活论坛排行
    @Select("select f.Y_FOR_NAME,f.Y_FOR_COUNT,g.Y_GRA_NAME,g.y_gra_image from Y_FORUM f INNER JOIN Y_GRADES g on f.Y_GRA_ID = g.Y_GRA_ID where f.Y_TYPE_ID=(select Y_TYPE_ID from Y_FORUM_TYPE where Y_TYPE_NAME='生活论坛') order by Y_FOR_COUNT DESC")
    List<YForum> getForumListByLive();


    //精华帖排行榜————右侧的新开论坛——根据论坛开设的时间查询出——论坛名称，发帖条数
    @Select("select Y_FOR_NAME,Y_FOR_COUNT from Y_FORUM  order by Y_FOR_DATE DESC ")
    List<YForum> getForumListByDate();

    //模糊查询操作————精华帖和业主论坛两个页面的搜索模块
    @Select("select * from Y_FORUM where Y_FOR_NAME like CONCAT('%',#{yForName},'%')")
    List<JSONObject> getForumlistByName(@Param("yForName") String yForName);


}
